package com.bankLogisticsSys.service;

import java.util.ArrayList;
import java.util.List;

import com.bankLogisticsSys.model.Expresscontent;
import com.bankLogisticsSys.model.Expressinfo;
import com.bankLogisticsSys.model.Expressprocess;
import com.bankLogisticsSys.model.Expressrecord;
import com.bankLogisticsSys.model.Packageinfo;
import com.bankLogisticsSys.model.Userinfo;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class ExpressService {

	/*
	 * get express record function
	 */
	public Expressrecord getRecord(int packageid){
		return Expressrecord.dao.findFirst("select * from expressrecord where packageID = ? order by time desc", packageid);
	}
	
	public Expressrecord getRecord(long trackingID,String employeeID,int packageid,int action){
		return Expressrecord.dao.findFirst("select * from expressrecord where trackingid = ? and employeeid = ? and packageid = ? and action = ?", trackingID, employeeID, packageid, action);
	}
	
	public List<Expressrecord> getRecordList(long trackingID, String employeeID,int action){
		List<Expressrecord> list = Expressrecord.dao.find("select * from expressrecord where trackingID = ? and employeeid = ? and action = ? order by packageid", trackingID, employeeID, action);
		if(list != null){
			return list;
		}else{
			list = new ArrayList<Expressrecord>();
			return list;
		}
	}
	
	/*
	 * get express info function
	 */
	public Expressinfo getInfoByTrackingID(long trackingID){
		return Expressinfo.dao.findFirst("select * from expressinfo where trackingID = ?", trackingID);
	}
	
	public Record getExpressInfo(long trackingID){
		String sql = "select id,if(flag=0,'下行','上行')flag,trackingID,"
				+ "(select network from networkinfo where id = expressinfo.source) as source, "
				+ "(select network from networkinfo where id = expressinfo.destination) as destination,"
				+ "sendDate "
				+ "from expressinfo where trackingID = ?";
		return Db.findFirst(sql, trackingID);
	}
	public Expressinfo getInfoByID(int id){
		return Expressinfo.dao.findFirst("select * from expressinfo where id = ?", id);
	}
	public Expressprocess getInfoByEmployee(String employeeID){
		return Expressprocess.dao.findFirst("select * from expressprocess where employeeid = ? order by id desc", employeeID);
	}
	
	//获取未完成的快递单
	public Expressinfo getNotFinishInfo(long trackingID){
		return Expressinfo.dao.findFirst("select * from expressinfo where trackingid = ? and state = 1", trackingID);
	}
	
	/*
	 * get express content function
	 */
	public Expresscontent getContentByPack(long trackingID, int packageID){
		return Expresscontent.dao.findFirst("select * from expresscontent where trackingID = ? and packageID = ? ", trackingID, packageID);
	}
	
	public List<Expresscontent> getContentList(long trackingID){
		return Expresscontent.dao.find("select * from expresscontent where trackingID = ?", trackingID);
	}
	public List<Record> getContentListCN(long trackingID){
		String  sql = "select expresscontent.packageid, if((select type from packageinfo where id = expresscontent.packageid)=0,'小包','大包')type from expresscontent where trackingID = ?";
		return Db.find(sql, trackingID);
	}
	
	public List<Expresscontent> getContentListB(long trackingID){
		return Expresscontent.dao.find("select * from expresscontent where trackingID = ? and bpackageid is null", trackingID);
	}
	
	public List<Record> getContentListBCN(long trackingID){
		String  sql = "select expresscontent.packageid, if((select type from packageinfo where id = expresscontent.packageid)=0,'小包','大包')type from expresscontent where trackingID = ? and bpackageid is null";
		return Db.find(sql, trackingID);
	}
	
	/*
	 * get express process function
	 */
	public Expressprocess getProcess(long trackingID){
		return Expressprocess.dao.findFirst("select * from expressprocess where trackingid = ? order by time desc", trackingID);
	}
	
	public Expressprocess getProcessUser(long trackingID,String employeeID,int action){
		return Expressprocess.dao.findFirst("select * from expressprocess where trackingid = ? and action = ? and employeeid = ?", trackingID, action,employeeID);
	}
	
	public List<Expressprocess> getProcessList(long trackingID){
		return Expressprocess.dao.find("select * from expressprocess where trackingid = ? order by time", trackingID);
	}
	
	public Packageinfo getPackageinfo(String packageCode){
		return Packageinfo.dao.findFirst("select * from packageinfo where packagecode = ?", packageCode);
	}
	
	/*
	 * get userinfo function
	 */
	public Userinfo getUserinfo(String openId){
		return Userinfo.dao.findFirst("select * from userinfo where openId = ?", openId);
	}
	public Record getUserinfoCN(String openId){
		String sql = "SELECT if(u.role=1,'银行网点员工',if(u.role=2,'分行员工',if(u.role=3,'揽投部',if(u.role=4,'分拨中心',if(u.role=5,'揽投员','其他角色')))))role,u.employeeID, u.password, u.name,u.telephone,u.department,n.network "
				+ "FROM userinfo as u "
				+ "join networkinfo as n "
				+ "ON u.networkid = n.id "
				+ "and u.openId = ?" ;
		return Db.findFirst(sql,openId);
	}
	public Userinfo getUserinfoByID(String employeeID){
		return Userinfo.dao.findFirst("select * from userinfo where employeeID = ?", employeeID);
	}
	public Userinfo getUserinfoByNetworkID(int networkid){
		return Userinfo.dao.findFirst("select * from userinfo where networkid = ? and role <> 5 and openid is not null", networkid);
	}
	
	public Boolean getFlag(long trackingID){
		Expressinfo ei = getInfoByTrackingID(trackingID);
		return ei.getFlag();
	}
	
}
